package com.psl.services.work.list;

import com.psl.Dao.Conn;
import com.psl.model.halfproduct;
import org.apache.struts2.ServletActionContext;

import javax.servlet.http.HttpServletRequest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class halfProductServices {
    public List Allprint(String h_ticketno) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        String sql = "SELECT DISTINCT hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n,hp.h_num,hp.h_uw,hp.h_dj,hp.h_date,hp.h_people,su.sp_name,hp.h_ticketno from halfproduct hp,supplier su where su.sp_id=hp.h_area";
        try {
            if (h_ticketno != null) {
                pstmt = conn.prepareStatement(sql + "  and h_ticketno=?");
                pstmt.setString(1, h_ticketno);
                rs = pstmt.executeQuery();
            }
            if (h_ticketno == null) {
                pstmt = conn.prepareStatement(sql);
                rs = pstmt.executeQuery();
            }
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_num(rs.getInt(6));
                p.setH_uw(rs.getFloat(7));
                p.setH_dj(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                product.add(p);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    public List selectprint(String h_area, String h_date) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        String sql1 = "SELECT hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n,sum(hp.h_num),hp.h_uw,hp.h_dj,hp.h_date,hp.h_people,su.sp_name,hp.h_ticketno from halfproduct hp,supplier su where ";
        String sql2 = " Group by hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n";
        try {
            if (((h_area == null ? 1 : 0) & (h_date == null ? 1 : 0)) != 0) {
                pstmt = conn.prepareStatement(sql1);
            }
            if (((h_area != null ? 1 : 0) & (h_date == null ? 1 : 0)) != 0) {
                pstmt = conn.prepareStatement(sql1 + "  hp.h_area=? AND su.sp_id=hp.h_area " + sql2);
                pstmt.setString(1, h_area);
            }
            if (((h_date != null ? 1 : 0) & (h_area == null ? 1 : 0)) != 0) {
                pstmt = conn.prepareStatement(sql1 + "  hp.h_date LIKE ? AND su.sp_id=hp.h_area" + sql2);
                pstmt.setString(1, h_date + "%");
            }
            if (((h_area != null ? 1 : 0) & (h_date != null ? 1 : 0)) != 0) {
                pstmt = conn.prepareStatement(sql1 + "  hp.h_area=? and hp.h_date LIKE ?  AND su.sp_id=hp.h_area  " + sql2);
                pstmt.setString(1, h_area);
                pstmt.setString(2, h_date + "%");
            }
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_num(rs.getInt(6));
                p.setH_uw(rs.getFloat(7));
                p.setH_dj(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                product.add(p);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("pl", product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List queryone(String h_id) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT h_id,h_no,h_name,h_size,h_r,h_n,h_num,h_uw,h_date,h_people,h_area,h_ticketno from proc_halfproduct WHERE h_id=?");
            pstmt.setString(1, h_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_id(rs.getInt(1));
                p.setH_no(rs.getString(2));
                p.setH_name(rs.getString(3));
                p.setH_size(rs.getString(4));
                p.setH_r(rs.getString(5));
                p.setH_n(rs.getString(6));
                p.setH_num(rs.getInt(7));
                p.setH_uw(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                product.add(p);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("query", product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List print(String h_area, String h_ticketno) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT hp.h_no, hp.h_name, hp.h_size, hp.h_r, hp.h_n, hp.h_dj, hp.h_num, hp.h_uw, hp.h_date, su.sp_name,hp.h_ticketno from halfproduct hp,supplier su  WHERE su.sp_id=? and hp.h_area=? and hp.h_ticketno=?");
            pstmt.setString(1, h_area);
            pstmt.setString(2, h_area);
            pstmt.setString(3, h_ticketno);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_dj(rs.getFloat(6));
                p.setH_num(rs.getInt(7));
                p.setH_uw(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_area(rs.getString(10));
                p.setH_ticketno(rs.getString(11));
                product.add(p);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("sp_name", p.getH_area());
                request.setAttribute("h_ticketno", p.getH_ticketno());
                request.setAttribute("h_date", p.getH_date());
                request.setAttribute("pro", product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List ptlist(int h_area) {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT  h_ticketno from halfproduct  where  h_area=? ORDER by h_ticketno ASC");
            pstmt.setInt(1, h_area);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_ticketno(rs.getString(1));
                product.add(p);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean InsertHalfProduct(String h_no, String h_name, String h_size, String h_r, String h_n, int h_num, float h_uw, String h_date, String h_people, String h_area, String h_ticketno, int status) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("insert into proc_halfproduct(h_no,h_name,h_size,h_r,h_n,h_num,h_uw,h_date,h_people,h_area,h_ticketno,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
            pstmt.setString(1, h_no);
            pstmt.setString(2, h_name);
            pstmt.setString(3, h_size);
            pstmt.setString(4, h_r);
            pstmt.setString(5, h_n);
            pstmt.setInt(6, h_num);
            pstmt.setFloat(7, h_uw);
            pstmt.setString(8, h_date);
            pstmt.setString(9, h_people);
            pstmt.setString(10, h_area);
            pstmt.setString(11, h_ticketno);
            pstmt.setInt(12, status);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean Addptkc(int h_id) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("{call kcpass(?)}");
            pstmt.setInt(1, h_id);
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }


    public boolean updateprocstatus(String h_ticketno) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("UPDATE proc_halfproduct set h_ticketno=?");
            pstmt.setString(1, h_ticketno);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public List querystatusback() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n,hp.h_num,hp.h_uw,hp.h_dj,hp.h_date,hp.h_people,su.sp_name,hp.h_ticketno,hp.h_id from proc_halfproduct hp,supplier su where su.sp_id=hp.h_area and hp.status=2");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_num(rs.getInt(6));
                p.setH_uw(rs.getFloat(7));
                p.setH_dj(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                p.setH_id(rs.getInt(13));
                product.add(p);
                HttpServletRequest request= ServletActionContext.getRequest();
                request.setAttribute("msg1","<a href='/halfproductback.do'>提示：您有<span style='color:red'> "+product.size()+"</span> 条暂收凭条被退回，请重新填写提交</a>");
                request.setAttribute("product",product);
            }
            return product;
        }
        catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List Allpt() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT hp.h_no, hp.h_name, hp.h_size, hp.h_r, hp.h_n, hp.h_num, hp.h_uw,hp.h_dj,hp.h_date,hp.h_people, su.sp_name,hp.h_ticketno from halfproduct hp,supplier su where su.sp_id=hp.h_area ");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_num(rs.getInt(6));
                p.setH_uw(rs.getFloat(7));
                p.setH_dj(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                product.add(p);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("pl", product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List querystatuswait() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT hp.h_id,hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n,hp.h_num,hp.h_uw,hp.h_dj,hp.h_date,hp.h_people,su.sp_name,hp.h_ticketno,hp.status from proc_halfproduct hp,supplier su where hp.status=0 and su.sp_id=hp.h_area");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_id(rs.getInt(1));
                p.setH_no(rs.getString(2));
                p.setH_name(rs.getString(3));
                p.setH_size(rs.getString(4));
                p.setH_r(rs.getString(5));
                p.setH_n(rs.getString(6));
                p.setH_num(rs.getInt(7));
                p.setH_uw(rs.getFloat(8));
                p.setH_dj(rs.getFloat(9));
                p.setH_date(rs.getString(10));
                p.setH_people(rs.getString(11));
                p.setH_area(rs.getString(12));
                p.setH_ticketno(rs.getString(13));
                p.setStatus(rs.getInt(14));
                product.add(p);
                HttpServletRequest request = ServletActionContext.getRequest();
                request.setAttribute("pt", product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public boolean updatestatusoff(int h_id) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("UPDATE proc_halfproduct set status=2 WHERE h_id=?");
            pstmt.setInt(1, h_id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean delproc(int h_id) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("DELETE from proc_halfproduct WHERE h_id=?");
            pstmt.setInt(1, h_id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }


    public boolean UpdateHalfProduct(int h_id, String h_no, String h_name, String h_size, String h_r, String h_n, int h_num, float h_uw, String h_date, String h_people, String h_area, int status) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("UPDATE proc_halfproduct SET h_no=?,h_name=?,h_size=?,h_r=?,h_n=?,h_num=?,h_uw=?,h_date=?,h_people=?,h_area=?,status=? WHERE h_id=?");
            pstmt.setString(1, h_no);
            pstmt.setString(2, h_name);
            pstmt.setString(3, h_size);
            pstmt.setString(4, h_r);
            pstmt.setString(5, h_n);
            pstmt.setInt(6, h_num);
            pstmt.setFloat(7, h_uw);
            pstmt.setString(8, h_date);
            pstmt.setString(9, h_people);
            pstmt.setString(10, h_area);
            pstmt.setInt(11, status);
            pstmt.setInt(12, h_id);
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public boolean addhalfproductList(String h_no, String h_name, String h_size, String h_r, String h_n) {
        Connection conn = new Conn().getConn();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("insert into halfproduct_list(h_no,h_name,h_size,h_r,h_n)  VALUES (?,?,?,?,?)");
            pstmt.setString(1, h_no);
            pstmt.setString(2, h_name);
            pstmt.setString(3, h_size);
            pstmt.setString(4, h_r);
            pstmt.setString(5, h_n);
            pstmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }


    public List today() {
        Connection conn = new Conn().getConn();
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        List product = new ArrayList();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
        String date=sdf.format(new Date());
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT hp.h_no,hp.h_name,hp.h_size,hp.h_r,hp.h_n,hp.h_num,hp.h_uw,hp.h_dj,hp.h_date,hp.h_people,su.sp_name,hp.h_ticketno from halfproduct hp,supplier su where su.sp_id=hp.h_area and hp.h_date=?");
            pstmt.setString(1,date);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                halfproduct p = new halfproduct();
                p.setH_no(rs.getString(1));
                p.setH_name(rs.getString(2));
                p.setH_size(rs.getString(3));
                p.setH_r(rs.getString(4));
                p.setH_n(rs.getString(5));
                p.setH_num(rs.getInt(6));
                p.setH_uw(rs.getFloat(7));
                p.setH_dj(rs.getFloat(8));
                p.setH_date(rs.getString(9));
                p.setH_people(rs.getString(10));
                p.setH_area(rs.getString(11));
                p.setH_ticketno(rs.getString(12));
                product.add(p);
                HttpServletRequest request=ServletActionContext.getRequest();
                request.setAttribute("today",product);
            }
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (rs != null) {
                    rs.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            }
        }
    }


}
